#!/bin/bash

APP=edu2077

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dim_user_zip="
use edu2077;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
    id,
    login_name,
    nick_name,
    real_name,
    phone_num,
    email,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    start_date,
    if(rn=2,date_sub('$do_date',1),end_date) end_date,
    if(rn=1,'9999-12-31',date_sub('$do_date',1)) dt
from
    (
        select
            id,
            login_name,
            nick_name,
            real_name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date,
            row_number() over (partition by id order by start_date desc) rn
        from
            (
                select
                    id,
                    login_name,
                    nick_name,
                    real_name,
                    phone_num,
                    email,
                    user_level,
                    birthday,
                    gender,
                    create_time,
                    operate_time,
                    start_date,
                    end_date
                from dim_user_zip
                where dt='9999-12-31'
                union
                select
                    id,
                    login_name,
                    nick_name,
                    md5(real_name) real_name,
                    md5(if(phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',phone_num,null)) phone_num,
                    md5(if(email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',email,null)) email,
                    user_level,
                    birthday,
                    gender,
                    create_time,
                    operate_time,
                    '2022-02-21' start_date,
                    '9999-12-31' end_date
                from
                    (
                        select
                            data.id,
                            data.login_name,
                            data.nick_name,
                            data.real_name,
                            data.phone_num,
                            data.email,
                            data.user_level,
                            data.birthday,
                            data.gender,
                            data.create_time,
                            data.operate_time,
                            row_number() over (partition by data.id order by ts desc) rn
                        from ods_user_info_inc
                        where dt='$do_date'
                    )t1
            )t2
    )t3;
"

dim_chapter_full="
use edu2077;
insert overwrite table ${APP}.dim_chapter_full partition(dt='$do_date')
select
       k1.chapter_id,
       k1.chapter_name,
       k1.publisher_id,
       k1.is_free,
       k1.create_time,
       k1.update_time,
       k2.course_id,
       k2.course_name,
       k2.subject_id,
       k2.subject_name,
       k2.category_id,
       k2.category_name
from
(
    select
        id chapter_id,
        chapter_name,
        publisher_id,
        is_free,
        create_time,
        update_time,
        course_id
    from ods_chapter_info_full
) k1
join
(
    select
        t1.course_id,
        t1.course_name,
        t2.subject_id,
        t2.subject_name,
        t3.category_id,
        t3.category_name
    from
    (
        select id course_id,
               course_name,
               subject_id
        from ods_course_info_full
        where dt = '$do_date'
    ) t1
    join
    (
        select
            id subject_id,
            subject_name ,
            category_id
        from ods_base_subject_info_full
    ) t2
    on t1.subject_id = t2.subject_id
    join
    (
        select
            id category_id,
            category_name
        from ods_base_category_info_full
    ) t3
    on t2.category_id = t3.category_id
) k2
on k1.course_id = k2.course_id;
"

dim_course_full="
insert overwrite table ${APP}.dim_course_full partition(dt='$do_date')
select distinct
	t5.id,
	course_name,
	course_slogan,
	publisher_id,
	origin_price,
	reduce_amount,
	actual_price,
	create_time,
	teacher,
	t6.subject_id,
	t6.subject_name,
	t6.category_id,
	t6.category_name,
	chapter_num,
    chapter_name,
	t7.video_id,
	t7.video_name,
	t7.during_sec,
    w1.paper_id,
    w1.paper_title
from
(
	select
		id,
		course_name,
		course_slogan,
		publisher_id,
		origin_price,
		reduce_amount,
		actual_price,
		create_time,
		teacher,
		subject_id,
		chapter_num
	from ods_course_info_full
	where dt='$do_date'
) t5
join
(
	select
		t1.id as subject_id,
		t1.subject_name,
		category_id,
		category_name
	from
	(
		select
			id,
			subject_name,
			category_id
		from ods_base_subject_info_full
		where dt='$do_date'
	) t1
	join
	(
		select
			id,
			category_name
		from ods_base_category_info_full
		where dt='$do_date'
	) t2
	on t1.category_id = t2.id
) t6
on t5.subject_id = t6.subject_id
right join
(
	select
		course_id,
		t3.chapter_name,
		video_id,
		video_name,
		during_sec
	from
	(
		select
			id,
		    chapter_name,
			course_id,
			video_id
		from ods_chapter_info_full
		where dt='$do_date'
	) t3
	join
	(
		select
			id ,
			video_name,
			during_sec
		from ods_video_info_full
		where dt='$do_date'
	) t4
	on t3.video_id = t4.id
) t7
on t5.id = t7.course_id
join
(
    select
        id paper_id,
        paper_title,
        course_id
    from ods_test_paper_full
) w1
on w1.course_id = t5.id;
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
select
    id,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from ods_base_province_full;
"

dim_question_full="
insert overwrite table ${APP}.dim_question_full partition(dt='$do_date')
select
    k3.id,
    k3.score,
    k3.publisher_id,
    k3.create_time,
    k3.paper_id,
    k3.paper_title,
    k4.question_id,
    k4.question_txt,
    k4.question_type,
    k4.option_id,
    k4.option_txt,
    k4.is_correct
from
(
    select
           k1.id,
           k1.score,
           k1.publisher_id,
           k1.create_time,
           k1.question_id,
           k1.paper_id,
           k2.paper_title
    from
    (
        select id,
               score,
               publisher_id,
               create_time,
               paper_title paper_id,
               question_id
        from ods_test_paper_question_full
        where dt = '$do_date'
    ) k1
    join
    (
        select
            id,
            paper_title
        from ods_test_paper_full
        where dt = '$do_date'
    ) k2
    on k1.paper_id = k2.id
) k3
join
(
    select
           t1.id question_id,
           question_txt,
           question_type,
           option_id,
           option_txt,
           is_correct
    from
    (
        select id,
               point_id question_txt,
               question_type
        from ods_test_question_info_full
        where dt = '$do_date'
    ) t1
    left join
    (
        select
            id option_id,
            option_txt,
            question_id,
            is_correct
        from ods_test_question_option_full
        where dt = '$do_date'
    ) t2
    on t1.id = t2.question_id
) k4
on k3.question_id = k4.question_id;
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_chapter_full")
    hive -e "$dim_chapter_full"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_question_full")
    hive -e "$dim_question_full"
;;
"all")
    hive -e "$dim_user_zip$dim_chapter_full$dim_course_full$dim_province_full$dim_question_full"
;;
esac